package com.lovo.bbs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.lovo.bbs.po.UserInfoPo;
import com.lovo.bbs.util.DbUtil;
import com.lovo.bbs.util.MysqlDb;
import com.lovo.bbs.util.StringUtil;

/**
 * 用户信息Dao
 * 
 * @author tiancen2001
 * 
 */
public class UserInfoDao { //操作类型
	 public static final int OPER_OF_UPDATE=2;
	 public static final int OPER_OF_DELETE=3;
	 
	 public UserInfoDao(){
		 
	 }
	

	/**
	 * 返回指定ID的用户Po
	 * 
	 * @param userName
	 * @param type
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  UserInfoPo getUserInfo(String userName, int type)
			throws NamingException, SQLException {
		UserInfoPo po = new UserInfoPo();

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select 	user.userid, user.username, user.email,"
				+ "user.qq, user.msn, user.blog, "
				+ "user.homepage, user.birthday, user.home, user.score, "
				+ "rank.rankname,rank.rankimg,"
				+ "userhead.headname,userhead.headimg,user.signature,user.topicnum,userhead.custom,user.usertype    "
				+ "from user left join rank on user.rankid=rank.rankid  "
				+ "left join userhead on user.headimgid=userhead.headid   "
				+ "where user.username=?  and  usertype>=?";

		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, userName);
		ps.setInt(2, type);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {// 找到
			po.setUserId(rs.getInt(1));
			po.setUserName(StringUtil.NoNull(rs.getString(2)));
			po.setEmail(rs.getString(3));

			po.setQq(StringUtil.NoNull(rs.getString(4)));
			po.setMsn(StringUtil.NoNull(rs.getString(5)));
			po.setBlog(StringUtil.NoNull(rs.getString(6)));

			po.setHomePage(StringUtil.NoNull(rs.getString(7)));
			po.setBirthday(StringUtil.NoNull(rs.getString(8)));
			po.setHome(StringUtil.NoNull(rs.getString(9)));
			po.setScore(rs.getInt(10));

			po.setRankName(rs.getString(11));
			po.setRankImg(rs.getString(12));

			po.setHeadName(rs.getString(13));
			po.setHeadImg(rs.getString(14));
			po.setSignature(rs.getString(15));

			po.setTopicNum(rs.getInt(16));

			po.setCustomHeadImg(rs.getInt(17) == 1 ? true : false);

			po.setType(rs.getInt(18));
		} else {
			po = null;
		}
		mysqldb.closePS(ps);
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return po;
	}

	/**
	 * 用户的积分增加2,发表数加一
	 * 
	 * @param authorname
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int addOneTopic(String authorname) throws NamingException,
			SQLException {
		int added = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update user set score=score+2,topicnum=topicnum+1 where  username='"
				+ authorname + "'";
		added = con.prepareStatement(sql).executeUpdate();

		mysqldb.closeConnection(con);

		return added;
	}

	/**
	 * 用户积分增加一
	 * 
	 * @param postAuthorName
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int addOnePost(String postAuthorName) throws SQLException,
			NamingException {
		int added = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update user set score=score+1 where  username='"
				+ postAuthorName + "'";
		added = con.prepareStatement(sql).executeUpdate();

		mysqldb.closeConnection(con);

		return added;
	}

	/**
	 * 修改邮件或密码
	 * 
	 * @param userpo
	 * @param formerpassword
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int chgMailPassword(UserInfoPo po, String formerpassword)
			throws NamingException, SQLException {
		int changed = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		if ("".equals(formerpassword)) {// 如果密码项没填,则只更新邮件
			String sql1 = "update user set email='" + po.getEmail()
					+ "'  where username='" + po.getUserName() + "'";
			changed = con.prepareStatement(sql1).executeUpdate();
		} else {// 否则查询输入的原密码是否正确,正确则更新密码和邮件
			String sql2 = "select password  from user where username='"
					+ po.getUserName() + "'";
			ResultSet rs = con.prepareStatement(sql2).executeQuery();
			rs.next();
			if (rs.getString(1).equals(formerpassword)) {// 原密码正确
				String sql3 = "update user set email='" + po.getEmail()
						+ "',password='" + po.getPassword()
						+ "'  where username='" + po.getUserName() + "'";
				changed = con.prepareStatement(sql3).executeUpdate();
			}
			mysqldb.closeRS(rs);
		}

		mysqldb.closeConnection(con);

		return changed;
	}

	/**
	 * 修改联系信息
	 * 
	 * @param uservo
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int chgContactInfo(UserInfoPo po) throws NamingException,
			SQLException {
		int changed = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "update user   "
				+ "set homepage=?,home=?,qq=?,msn=?,blog=?  "
				+ "where username=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, po.getHomePage());
		ps.setString(2, po.getHome());
		ps.setString(3, po.getQq());
		ps.setString(4, po.getMsn());
		ps.setString(5, po.getBlog());
		ps.setString(6, po.getUserName());
		changed = ps.executeUpdate();

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);

		return changed;
	}

	/**
	 * 
	 * @param po
	 *            包含新头像
	 * @param headImg
	 *            原头像
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int updateUserHead(UserInfoPo po, String headImg)
			throws NamingException, SQLException {
		int changed = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		con.setAutoCommit(false);

		// 首先更新userhead表,如果原头像是上传的图像,则删除原头像记录
		String tempImg = null;
		if (headImg.startsWith("upload\\")) {
			// 如果是上传的包含路径的图像,则必须将反单斜杠替换成反双斜杠,SQL语句才能正常识别
			tempImg = headImg.replace("\\", "\\\\");
		} else {
			tempImg = headImg;
		}
		String sqlDelHead = "delete from userhead where headimg='" + tempImg
				+ "'  and  custom='1'";
		con.prepareStatement(sqlDelHead).executeUpdate();
		con.commit();

		// 再次更新userhead表,如果新头像是上传的头像,则插入一条记录,并查询该记录的ID值
		int newID = 0;
		if (po.isCustomHeadImg()) {
			// 插入记录
			String sqlInsertHead = "insert into userhead(headname, headimg, custom)"
					+ "values(?,?,'1')";
			PreparedStatement ps2 = con.prepareStatement(sqlInsertHead);
			ps2.setString(1, po.getHeadImg());
			ps2.setString(2, po.getHeadImg());
			ps2.executeUpdate();
			con.commit();
			mysqldb.closePS(ps2);

			// 查询记录ID
			String sqlQueryMaxId = "select max(headid)  from userhead";
			ResultSet rs = con.prepareStatement(sqlQueryMaxId).executeQuery();
			rs.next();
			newID = rs.getInt(1);
			mysqldb.closeRS(rs);
		}

		// 更新user表,此处必须用PreparedStatement,
		// 否则可能遭SQL注入攻击,
		// 只需在个性签名的任意地方加上一个单引号,就可使系统崩溃
		String sqlUpdateUser = null;
		if (po.isCustomHeadImg()) {// 如果是上传图像
			sqlUpdateUser = "update user set  signature=?,headimgid=?  where username=?";
			PreparedStatement ps3 = con.prepareStatement(sqlUpdateUser);
			ps3.setString(1, po.getSignature());
			ps3.setInt(2, newID);
			ps3.setString(3, po.getUserName());
			changed = ps3.executeUpdate();
		} else {
			sqlUpdateUser = "update user set  signature=?,   "
					+ "headimgid=(select  headid from userhead where headimg=?)    "
					+ "where username=?   ";
			PreparedStatement ps4 = con.prepareStatement(sqlUpdateUser);
			ps4.setString(1, po.getSignature());
			ps4.setString(2, po.getHeadImg());
			ps4.setString(3, po.getUserName());
			changed = ps4.executeUpdate();
		}
		con.commit();
		mysqldb.closeConnection(con);
		return changed;
	}

	/**
	 * 返回所有用户
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<UserInfoPo> getUsers(int page)
			throws NamingException, SQLException {
		ArrayList<UserInfoPo> pos = new ArrayList<UserInfoPo>();

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		// 数据库分页边界
		int startIndex = (page - 1) * 20;
		int endIndex = (page - 1) * 20 + 20;

		String sql = "select 	user.userid, user.username, user.email,"
				+ "user.qq, user.msn, user.blog, "
				+ "user.homepage, user.birthday, user.home, user.score, "
				+ "rank.rankname,rank.rankimg,"
				+ "userhead.headname,userhead.headimg,user.signature,user.topicnum,userhead.custom,user.usertype     "
				+ "from user left join rank on user.rankid=rank.rankid  "
				+ "left join userhead on user.headimgid=userhead.headid   "
				+ "order by  userid  limit  " + startIndex + "," + endIndex;
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		while (rs.next()) {
			UserInfoPo po = new UserInfoPo();

			po.setUserId(rs.getInt(1));
			po.setUserName(StringUtil.NoNull(rs.getString(2)));

			po.setEmail(rs.getString(3));

			po.setQq(StringUtil.NoNull(rs.getString(4)));
			po.setMsn(StringUtil.NoNull(rs.getString(5)));
			po.setBlog(StringUtil.NoNull(rs.getString(6)));

			po.setHomePage(StringUtil.NoNull(rs.getString(7)));
			po.setBirthday(StringUtil.NoNull(rs.getString(8)));
			po.setHome(StringUtil.NoNull(rs.getString(9)));

			po.setScore(rs.getInt(10));

			po.setRankName(rs.getString(11));
			po.setRankImg(rs.getString(12));

			po.setHeadName(rs.getString(13));
			po.setHeadImg(rs.getString(14));
			po.setSignature(rs.getString(15));

			po.setTopicNum(rs.getInt(16));

			po.setCustomHeadImg(rs.getInt(17) == 1 ? true : false);

			po.setType(rs.getInt(18));

			pos.add(po);
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return pos;
	}

	/**
	 * 计算用户数
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getUserNum() throws NamingException, SQLException {
		int userNum = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select count(*)  from user";
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		rs.next();
		userNum = rs.getInt(1);

		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return userNum;
	}

	/**
	 * 控制台更新
	 * @throws SQLException 
	 * @throws NamingException 
	 */
	public  int updateUser(UserInfoPo po, int operType) throws NamingException, SQLException {
		int updated = 0;
		
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		con.setAutoCommit(false);
		
		if(operType == UserInfoDao.OPER_OF_UPDATE){
			String sqlUpdate = "update  user  set  email=?,score=?,topicnum=?  where  userid=?";
			PreparedStatement ps = con.prepareStatement(sqlUpdate);
			ps.setString(1, po.getEmail());
			ps.setInt(2, po.getScore());
			ps.setInt(3, po.getTopicNum());
			ps.setInt(4, po.getUserId());
			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		}else if(operType==TopicDao.OPER_OF_DELETE){
			String sqlDelete = "delete from user  where userid=?";
			PreparedStatement ps = con.prepareStatement(sqlDelete);
			ps.setInt(1, po.getUserId());
			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		}
		con.commit();
		mysqldb.closeConnection(con);
		return updated;
	}

	/**
	 * 删除用户
	 * @param userid
	 * @return
	 * @throws SQLException 
	 * @throws NamingException 
	 */
	public  int delUser(int userid) throws NamingException, SQLException {
		int del = 0;
		
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		
		String sql = "delete from user where userid="+userid;
		del=con.prepareStatement(sql).executeUpdate();
		
		mysqldb.closeConnection(con);
		return del;
	}
}
